<?php
function list_karyawan_realisasi($periode_id){ 
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = (int) date('Y' , strtotime($periode['tanggal']));
	$end=$periode['tahun'].'-'. sprintf( '%02d',$bulan) .'-10' ;
	$header1 = array(
		'#'=>array('style'=>'border-bottom:2px solid;width:3%'),
		'nik'=>array('style'=>'border-bottom:2px solid;width:8%'),
		'nama'=>array('style'=>'border-bottom:2px solid;width:20%'), 
	);
	$header2= array();
	$tanggals = list_kalender($periode['dari'] , $periode['sampai']  );
	$tanggals = array_unique($tanggals) ;
 
	foreach($tanggals as $tgl){ 
		$header2[date('d',strtotime($tgl) )] = array('style'=>'border-bottom:2px solid;width:1%');
	}
	$header3 = array(' '=>array('style'=>'border-bottom:2px solid;width:3%'));
	$header = $header1 + $header2 /*+ $header3*/;
	if(isset($_GET['key'])){
		$query = "
			SELECT * FROM karyawan a 
			INNER JOIN biodata b 
			ON a.biodata_id = b.biodata_id
			WHERE a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)	
			AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE TMT <= '{$end}') 	
			AND ( b.nama LIKE '%{$_GET['key']}%' OR a.nik='{$_GET['key']}')
			ORDER BY a.nik
		";
	}else{
		$query = "
			SELECT * FROM karyawan a 
			INNER JOIN biodata b 
			ON a.biodata_id = b.biodata_id
			WHERE a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)	
			AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE TMT <= '{$end}') 	
			ORDER BY a.nik
		";
	}
	$result = my_query($query);
	$total_records = my_num_rows($result );
	$scroll_page =5;   
	$per_page =24;  
	$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 1 ; 
	if($current_page < 1){
		$current_page = 1;
	}		 
	$pager_url  ="index.php?com={$_GET['com']}&task={$_GET['task']}&key={$_GET['key']}&page=";	 
	$inactive_page_tag = 'style="padding:4px;background-color:#BBBBBB"';  
	$previous_page_text = ' Mundur '; 
	$next_page_text = ' Maju ';  
	$first_page_text = ' Awal '; 
	$last_page_text = ' Akhir ';
	
	$kgPagerOBJ = new kgPager();
	$kgPagerOBJ->pager_set(
		$pager_url, 
		$total_records, 
		$scroll_page, 
		$per_page, 
		$current_page, 
		$inactive_page_tag, 
		$previous_page_text, 
		$next_page_text, 
		$first_page_text, 
		$last_page_text ,$pager_url_last); 
	 		
	$result = my_query($query ." LIMIT ".$kgPagerOBJ->start.", ".$kgPagerOBJ->per_page); 	
	$row = array(); 
	$i = ($current_page  - 1 ) * 24; 
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']); 
 
		$detailproperty = array(
			'href'=>'index.php?com='.$_GET['com'].'&task=list_detail&karyawan_id='.$ey['karyawan_id'], 
			'title'=>'Detail'
		);
		$detail_button = button_icon( 'b_props.png' , $detailproperty  ); 
		$row1 = array(
			'#'=>position_text_align ($i, 'center'), 
			'nik'=>position_text_align( $karyawan['nik'], 'center'),
			'nama'=>substr($karyawan['nama_gelar'], 0,12), 
		//	'wk'=>position_text_align( $karyawan['waktu_kerja'] , 'center'), 
		//	'kel'=>position_text_align( $karyawan['kelompok'] , 'center'), 
			 
			 
		);
		$row2  = array(); 

		foreach($tanggals as $tgl){ 
				$ds =get_realisasi_hadir( $tgl , $ey['karyawan_id']  ); 
				list( $yyt,$mmt,$tgldd)  = explode("-", $tgl);
				$row2[date('d',strtotime($tgl) )] =  position_text_align( $ds  , 'right'); 
		 
		}
		$row3   = array(' '=> position_text_align( $detail_button  , 'right'));
		$row[] = array_merge( $row1 , $row2 /* , $row3 */);
	}
	
	$datas = table_rows($row); 
	$paging = $kgPagerOBJ ->showPaging();
 
	$search = '<form method="GET"><input type="hidden" name="com" value="'.$_GET['com'].'" />
	<span style="font:10px verdana"><b>Pencarian cepat</b></span> 
	<input type="hidden" name="periode_id" value="'.$periode_id.'"/>
	<br/><input type="text"  style="width:200px;" name="key" value="'.(isset($_GET['key']) ? $_GET['key']: "").'" />
	<input class="simple_search" type="submit" value=" CARI " /> </form>';

	$view ='<table width="90%" cellspacing="0" cellpadding="4"><tr>'; 
	$view .='<table width="100%"   style="border-collapse:collapse;border-color:white" cellspacing="0" cellpadding="2">';
	//$view .= form_field_display( '<span class="label_form"><b>'.date('m-Y' $periode_gajis['nama_komponen_gaji']).'</b></span>', "<b>Komponen Gaji</b>"    ); 
	
	//$button_import =   ($komponen_gajis['reset_tutup_bulan'] =='yes') ? '';
	$view .= '<tr><td><br/>'.$search .'</td><td valign="bottom"><div style="width:100%;text-align:right;">
	'.$button_import .' 
	<input type="button" value="Excel" onclick="javascript:location.href=\'index.php?com='.$_GET['com'].'&periode_id='.$periode_id.'&task=excel&nik_start='.$_GET['nik_start'].'&nik_end='.$_GET['nik_end'].'\'" class="main_button" /> 
	<!--input type="button" value="Reset" onclick="javascript:confirmRst()" class="main_button" /--> 
	<!--input type="button" value="Proses" onclick="javascript: jQuery.facebox({ ajax: \'index.php?com='.$_GET['com'].'&task=set_proses&id=0\' });" class="main_button" /--> 
	 </div></td></tr>'   ; 	 
	return  $view. table_builder($header , $datas ,  15 ,false ,$paging );
}


function get_realisasi_hadir($date , $karyawan_id  ){
	list($yyyy,$mm,$dd) = explode("-" , $date);
	$dd = (int) $dd;
	$query = "SELECT status_absen FROM  temp_tmn_hari 
		WHERE karyawan_id = {$karyawan_id} AND tgl_index = '{$dd}'   ";
  
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['status_absen'];
}

function get_realisasi_karyawan(  $karyawan_id){
	list($yyyy,$mm,$dd) = explode("-" , $date);
	$dd = (int) $dd;
	$query = "SELECT tgl_index , status_absen FROM  tmn_hari_realisasi
		WHERE karyawan_id = {$karyawan_id}";
	$result = my_query($query);
	$datas = array();
	while($row = my_fetch_array($result)){
		$dx = sprintf('%02d',$row['tgl_index']);
		$datas[$dx] = $row['status_absen'];
	}
	return $datas;
}

function get_kelompok_jadwal_by_upload(   $karyawan_id ){
	$query = "SELECT * FROM tmn_jadwal_karyawan WHERE karyawan_id = {$karyawan_id}";
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row;
}
  

function excel_hari_kerja($periode_id){	
	$periode = my_get_data_by_id('pr_periode','periode_id',$periode_id);
	$bulan = (int) date('m' , strtotime($periode['tanggal']));
	$tahun = (int) date('Y' , strtotime($periode['tanggal']));
	$end=$periode['tahun'].'-'. sprintf( '%02d',$bulan) .'-10' ;
	$header1 = array( 
		'nik'=>array('style'=>'border-bottom:2px solid;width:8%'),
		'nama'=>array('style'=>'border-bottom:2px solid;width:20%'), 
	);
	$header2= array();
	$tanggals = list_kalender($periode['dari'] , $periode['sampai']  );
	$tanggals = array_unique($tanggals) ;
	foreach($tanggals as $tgl){ 
		$header2[date('d',strtotime($tgl) )] = array('style'=>'border-bottom:2px solid;width:1%');
	}
	$header3 = array(' '=>array('style'=>'border-bottom:2px solid;width:3%'));
	$header = $header1 + $header2;
	
	$query = " SELECT * FROM temp_tmn_hari  a
				INNER JOIN karyawan b ON b.karyawan_id = a.karyawan_id 
				WHERE  b.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
				AND b.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  )
				GROUP BY a.karyawan_id ORDER BY b.nik";
	$result = my_query($query);
	$row = array(); 
	while($ey = my_fetch_array($result )){
	 	$karyawan =  loaddata_karyawan($ey['karyawan_id']); 
 
		$detailproperty = array(
			'href'=>'index.php?com='.$_GET['com'].'&task=list_detail&karyawan_id='.$ey['karyawan_id'], 
			'title'=>'Detail'
		);
		$detail_button = button_icon( 'b_props.png' , $detailproperty  ); 
		$row1 = array( 
			'nik'=> "'".$karyawan['nik'],  
			'nama'=> $karyawan['nama'],  
			 
		);
		$row2  = array(); 

		foreach($tanggals as $tgl){ 
				list( $yyt,$mmt,$tgldd)  = explode("-", $tgl);
				$ds =get_realisasi_hadir($tgl, $ey['karyawan_id']); 
				$row2[date('d',strtotime($tgl) )] =    $ds   ; 
		 
		}
		$row3   = array(' '=> position_text_align( $detail_button  , 'right'));
		$row[] = array_merge( $row1 , $row2 /* , $row3 */);
	}
 

	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas ,33 ,false ); 	
}

function excel_print_finger_log(){
	$queryt = " SELECT  * FROM pr_periode WHERE gaji='Active' LIMIT 1";
	$resultt = my_query($queryt);
	$data = my_fetch_array($resultt);
	$start_date = $data['dari'];
	$end_date 	= $data['sampai'];
	
	$header = array(
		'NIK'=>array(),  
		'Nama'=>array(),   
		'SwapDatetime'=>array() 
	);
	
	$query = " SELECT  
					a.karyawan_mesin_id , 
					b.lokasi , a.datetime_mesin_log
				FROM a_mesin_capture a 
				INNER JOIN a_mesin_lokasi b
					ON a.mesin_lokasi_id = b.id
				WHERE manual = 'N' AND 
					( DATE(a.datetime_mesin_log) BETWEEN '{$start_date}' AND '{$end_date}' )
			 ORDER BY a.karyawan_mesin_id , a.datetime_mesin_log
				";
	$result = my_query($query);
	while($ey = my_fetch_array($result )){ 
		$karyawan = loaddata_karyawan($ey['karyawan_mesin_id']);
		$row[] = array( 
			'nik'=>  "'".sprintf('%07d',$karyawan['nik']),    
			'nama'=>ucfirst($karyawan['nama']),
			'baru'=>  $ey['datetime_mesin_log'] 
		);
	}
	
	$datas = table_rows_excel($row); 
	header("Content-Type: application/xls");
	header("Content-Disposition: attachment;filename=data_absen_mesin.xls");
	echo table_builder_excel($header , $datas ,4 ,false ); 
	exit;
}